import pandas as pd
import pymysql


class StudentMapper:
    def __init__(self):
        self.host = "localhost"
        self.port = 3306
        self.user = "root"
        self.password = "123456"
        self.database = "student_portrait_system"
        self.conn = pymysql.connect(host=self.host, port=self.port, user=self.user, password=self.password,
                                    database=self.database)
        self.cursor = self.conn.cursor()

    # 根据学生ID获取学生信息
    def get_student_info_by_id(self, student_id):
        sql = "SELECT * FROM stu_info WHERE stu_id = %s"
        self.cursor.execute(sql, (student_id,))
        result = self.cursor.fetchall()
        if result:
            return result
        else:
            return None

    # 获取所有学生信息返回为dataframe
    def get_all_student_info(self):
        sql = "SELECT * FROM stu_info"
        data = pd.read_sql(sql, self.conn)
        if not data.empty:
            return data
        else:
            return False

    # 课程推荐方法
    def recommend_course(self, student_id):
        # 根据学生的id找到对应的记录，记录中的label字段是聚类标签，找到所有记录中label相同的记录，然后作为推荐课程
        sql = "SELECT * FROM k_means_result WHERE stu_id = %s"
        self.cursor.execute(sql, (student_id,))
        result = self.cursor.fetchall()
        if result:
            label = result[0][-1]
            sql = "SELECT * FROM k_means_result WHERE label = %s"
            self.cursor.execute(sql, (label,))
            result = self.cursor.fetchall()
            if result:
                return result
            else:
                return None
        else:
            return None
